using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.DB.Orm
{
    /// <summary></summary>
    public class OrmWorker:IWorker
    {
        private Thread _WorkerThread;
        private OrmInstruction _Instruction;
        private OrmOutput _Output;
        private Guid _TicketID;
        private RunContext _Context;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return this._Output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(OrmComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this._Instruction = (OrmInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._Context = context;
            this._TicketID = ticketGuid;
            this._Instruction = (OrmInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if(this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.PerformDbAction();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.PerformDbAction));
            this._WorkerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if(this._WorkerThread !=null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                if(this.WorkerCancelledEvent !=null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this._Instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #region work
        private void PerformDbAction()
        {
            try
            {
                object instance = this._Instruction.BindableObject;
                switch(this._Instruction.ActionType)
                {
                    default:
                        break;
                }

                this._Output =
                        new OrmOutput(this._Instruction.OwnerComponentID,
                                      this._TicketID,
                                      instance);
                    
                if(this.WorkerFinishedEvent !=null)
                {
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
        }
        #endregion

        #region insert
        private void InsertObj(ref object instance)
        {
            SqlConnection conn=new SqlConnection(this._Instruction.ConnStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                bool schemaValidated = this.ValidateSchema();
                if(!schemaValidated )
                {
                    throw new Exception("Object and table schema do not agree");
                }

                List<TableColumn> cols=new List<TableColumn>();
                bool tblExist = DbUtil.IsTableExist(DataProviderType.MSSQL, this._Instruction.ConnStr, this._Instruction.TblName);
                if(!tblExist)
                {
                    cols = this.GetColumnsForAllObjectFields();
                }
                else
                {
                    TableColumn[] tblCols =
                        DbUtil.GetTableColumns(DataProviderType.MSSQL, this._Instruction.ConnStr, string.Empty,
                                               this._Instruction.TblName);
                    cols.AddRange(tblCols);
                }
                // remove identity primary key
                if(this._Instruction.IsIdentity)
                {
                    for(int i=0;i<cols.Count;i++)
                    {
                        if(cols[i].ColumnName.ToLower()==this._Instruction.PK)
                        {
                            cols.RemoveAt(i);
                            break;
                        }
                    }
                }

                string spName = "tmpSp_Insert" + this._Instruction.TblName;
                string dropSp = StoredProcUtil.CreateDropStoredProcedureStatement(DataProviderType.MSSQL, spName);
                cmd.CommandText = dropSp;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                string createSp = string.Empty;
                if (this._Instruction.IsIdentity)
                {
                    createSp = StoredProcUtil.CreateStoredProcedureStatementForIdentityInsert(DataProviderType.MSSQL,
                                                                                               spName,
                                                                                               string.Empty,
                                                                                               this._Instruction.TblName,
                                                                                               cols.ToArray(),
                                                                                               this._Instruction.PK);
                }
                else
                {
                    createSp = StoredProcUtil.CreateStoredProcedureStatementForInsert(DataProviderType.MSSQL,
                                                                                       spName, string.Empty,
                                                                                       this._Instruction.TblName,
                                                                                       cols.ToArray());
                }
                cmd.CommandText = createSp;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                // Dictionary<string,SqlParameter> colNameParameters=new Dictionary<string, SqlParameter>();
                // boxing 
                SqlParameter outputParameter = null;
                foreach(TableColumn col in cols)
                {
                    SqlParameter parameter =
                        new SqlParameter("@" + StoredProcUtil.TidyObjectName(col.ColumnName), DBNull.Value);
                    FieldInfo fieldInfo = instance.GetType().GetField(col.ColumnName);
                    if(fieldInfo !=null)
                    {
                        object fieldValue = fieldInfo.GetValue(instance);
                        if(fieldValue !=null)
                        {
                            parameter.Value = fieldValue;
                        }
                    }
                    else
                    {
                        PropertyInfo propInfo = instance.GetType().GetProperty(col.ColumnName);
                        if(propInfo !=null && propInfo.CanRead)
                        {
                            object propValue = propInfo.GetValue(instance, null);
                            parameter.Value = propValue;
                        }
                    }
                    if(this._Instruction.IsIdentity && col.ColumnName.ToLower()==this._Instruction.PK.ToLower())
                    {
                        parameter.Direction = ParameterDirection.Output;
                        outputParameter = parameter;
                    }
                    cmd.Parameters.Add(parameter);
                }
                cmd.ExecuteNonQuery();
                if(this._Instruction.IsIdentity && outputParameter !=null)
                {
                    int pkValue = int.Parse(outputParameter.Value.ToString());
                    FieldInfo pkFieldInfo = instance.GetType().GetField(this._Instruction.PK);
                    if(pkFieldInfo !=null)
                    {
                        pkFieldInfo.SetValue(instance,pkValue);
                    }
                    else
                    {
                        PropertyInfo pkPropInfo = instance.GetType().GetProperty(this._Instruction.PK);
                        if(pkPropInfo !=null && pkPropInfo.CanWrite)
                        {
                            pkPropInfo.SetValue(instance,pkValue,null);
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            finally
            {
                conn.Close();
            }
        }

        private void InsertObjectCollection(ref object[] instances)
        {
            if(instances ==null || instances.Length==0)
                throw new Exception("Object collection is empty or not initialized");

            SqlConnection conn = new SqlConnection(this._Instruction.ConnStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                bool schemaValidated = this.ValidateSchema();
                if (!schemaValidated)
                {
                    throw new Exception("Object and table schema do not agree");
                }

                List<TableColumn> cols = new List<TableColumn>();
                bool tblExist = DbUtil.IsTableExist(DataProviderType.MSSQL, this._Instruction.ConnStr, this._Instruction.TblName);
                if (!tblExist)
                {
                    cols = this.GetColumnsForAllObjectFields();
                }
                else
                {
                    TableColumn[] tblCols =
                        DbUtil.GetTableColumns(DataProviderType.MSSQL, this._Instruction.ConnStr, string.Empty,
                                               this._Instruction.TblName);
                    cols.AddRange(tblCols);
                }
                // remove identity primary key
                if (this._Instruction.IsIdentity)
                {
                    for (int i = 0; i < cols.Count; i++)
                    {
                        if (cols[i].ColumnName.ToLower() == this._Instruction.PK)
                        {
                            cols.RemoveAt(i);
                            break;
                        }
                    }
                }

                string spName = "tmpSp_Insert" + this._Instruction.TblName;
                string dropSp = StoredProcUtil.CreateDropStoredProcedureStatement(DataProviderType.MSSQL, spName);
                cmd.CommandText = dropSp;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                string createSp = string.Empty;
                if (this._Instruction.IsIdentity)
                {
                    createSp = StoredProcUtil.CreateStoredProcedureStatementForIdentityInsert(DataProviderType.MSSQL,
                                                                                               spName,
                                                                                               string.Empty,
                                                                                               this._Instruction.TblName,
                                                                                               cols.ToArray(),
                                                                                               this._Instruction.PK);
                }
                else
                {
                    createSp = StoredProcUtil.CreateStoredProcedureStatementForInsert(DataProviderType.MSSQL,
                                                                                       spName, string.Empty,
                                                                                       this._Instruction.TblName,
                                                                                       cols.ToArray());
                }
                cmd.CommandText = createSp;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                Dictionary<string,SqlParameter> colNameParameters=new Dictionary<string, SqlParameter>();
                Type objType = instances[0].GetType();
                foreach (TableColumn col in cols)
                {
                    SqlParameter parameter =
                        new SqlParameter("@" + StoredProcUtil.TidyObjectName(col.ColumnName), DBNull.Value);
                    colNameParameters.Add(col.ColumnName, parameter);
                    
                    if (this._Instruction.IsIdentity && col.ColumnName.ToLower() == this._Instruction.PK.ToLower())
                    {
                        parameter.Direction = ParameterDirection.Output;
                    }
                }

                for(int i=0;i<instances.Length;i++)
                {
                    try
                    {
                        cmd.Parameters.Clear();
                        foreach(TableColumn col in cols)
                        {
                            SqlParameter parameter = colNameParameters[col.ColumnName];
                            FieldInfo fieldInfo = objType.GetField(col.ColumnName);
                            if (fieldInfo != null)
                            {
                                object fieldValue = fieldInfo.GetValue(instances[i]);
                                if (fieldValue != null)
                                {
                                    parameter.Value = fieldValue;
                                }
                            }
                            else
                            {
                                PropertyInfo propInfo = objType.GetProperty(col.ColumnName);
                                if (propInfo != null && propInfo.CanRead)
                                {
                                    object propValue = propInfo.GetValue(instances[i], null);
                                    parameter.Value = propValue;
                                }
                            }
                            cmd.Parameters.Add(parameter);
                        }
                        cmd.ExecuteNonQuery();

                        // get identity pk value
                        if (this._Instruction.IsIdentity)
                        {
                            SqlParameter outputParameter = colNameParameters[this._Instruction.PK];
                            int pkValue = int.Parse(outputParameter.Value.ToString());
                            FieldInfo pkFieldInfo = instances[i].GetType().GetField(this._Instruction.PK);
                            if (pkFieldInfo != null)
                            {
                                pkFieldInfo.SetValue(instances[i], pkValue);
                            }
                            else
                            {
                                PropertyInfo pkPropInfo = objType.GetProperty(this._Instruction.PK);
                                if (pkPropInfo != null && pkPropInfo.CanWrite)
                                {
                                    pkPropInfo.SetValue(instances[i], pkValue, null);
                                }
                            }
                        }

                    }
                    catch(Exception ex1)
                    {
                        if(this.WorkerErredEvent !=null)
                        {
                            this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex1.ToString(),true);
                        }
                    }

                    if(this.WorkerStatusChangedEvent !=null && i % 100==0)
                    {
                        string msg =
                            string.Format("Add records to table {0}, #{1} of {2}", this._Instruction.TblName, i,
                                          instances.Length);
                        int percent = (i*100)/instances.Length;
                        this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this._Instruction,
                            this.WorkflowName, this.ComponentName, msg, percent);
                    }
                }
            }
            catch (Exception ex)
            {
                if (this.WorkerErredEvent != null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region update

        #endregion

        #region delete

        #endregion

        #region get
        private object GetObjectByID(object idValue)
        {
            throw new NotImplementedException();
        }

        private object[] GetObjectCollectionByForeignKey(string fkName, object fkValue)
        {
            throw new NotImplementedException();
        }
        #endregion

        #region schema
        private bool ValidateSchema()
        {
            bool schemaValidated = false;
            try
            {
                bool tableExist = DbUtil.IsTableExist(DataProviderType.MSSQL, this._Instruction.ConnStr, this._Instruction.TblName);
                if(!tableExist )
                {
                    // create table 
                    List<TableColumn> cols = this.GetColumnsForAllObjectFields();
                    if(cols ==null || cols.Count==0)
                        throw new Exception("Unable to find bindable fields within object type");
                    
                    TableHelper.CreateTable(this._Instruction.ConnStr,this._Instruction.TblName,cols.ToArray(),false);
                    schemaValidated = true;
                }
                else
                {
                    TableColumn[] cols =
                        DbUtil.GetTableColumns(DataProviderType.MSSQL, this._Instruction.ConnStr, string.Empty,
                                               this._Instruction.TblName);
                    List<string> memberNames=new List<string>();
                    FieldInfo[] fields = this._Instruction.BindableObject.GetType().GetFields();
                    foreach (FieldInfo field in fields)
                    {
                        if (field.IsPublic && !field.IsStatic)
                        {
                            memberNames.Add(field.Name.ToLower());
                        }
                    }

                    PropertyInfo[] props = this._Instruction.BindableObject.GetType().GetProperties();
                    foreach (PropertyInfo prop in props)
                    {
                        memberNames.Add(prop.Name.ToLower());
                    }

                    bool foundMismatch = false;
                    foreach(TableColumn col in cols)
                    {
                        if(!memberNames.Contains(col.ColumnName.ToLower()))
                        {
                            foundMismatch = true;
                            break;
                        }
                    }
                    if(!foundMismatch)
                        schemaValidated = true;
                }
            }
            catch (Exception ex)
            {
                if (this.WorkerErredEvent != null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            
            return schemaValidated;
        }

        private List<TableColumn> GetColumnsForAllObjectFields()
        {
            List<TableColumn> cols = new List<TableColumn>();
            FieldInfo[] fields = this._Instruction.BindableObject.GetType().GetFields();
            foreach (FieldInfo field in fields)
            {
                if (field.IsPublic && !field.IsStatic)
                {
                    TableColumn col = new TableColumn();
                    col.ColumnName = field.Name;
                    col.AllowNull = true;
                    col.DbType = FieldDataType.ToDbType(field.FieldType.Name);
                    col.IsFK = false;
                    col.IsIdentity = false;
                    col.IsIndexed = false;
                    col.IsPK = false;
                    col.Ordinal = cols.Count;
                    col.Precision = 0;
                    col.Scale = 0;
                    col.Size = 255;
                    if (FieldDataType.IsBinaryType(col.DbType))
                    {
                        col.Size = 400000000;
                    }
                    else if (FieldDataType.isBoolean(col.DbType))
                    {
                        col.Size = 1;
                    }
                    else if (FieldDataType.IsDateTime(col.DbType))
                    {
                        col.Size = 8;
                    }
                    else if (FieldDataType.IsNumber(col.DbType))
                    {
                        col.Size = 8;
                    }
                    else if (FieldDataType.IsStringType(col.DbType))
                    {
                        col.Size = 255;
                    }
                    else if (FieldDataType.IsTextType(col.DbType))
                    {
                        col.Size = 400000000;
                    }

                    if (this._Instruction.PK.ToLower() == col.ColumnName.ToLower())
                    {
                        col.IsPK = true;
                        col.IsIdentity = this._Instruction.IsIdentity;
                        col.IsIndexed = true;
                    }

                    cols.Add(col);
                }
            }

            PropertyInfo[] props = this._Instruction.BindableObject.GetType().GetProperties();
            foreach (PropertyInfo prop in props)
            {
                TableColumn col = new TableColumn();
                col.ColumnName = prop.Name;
                col.AllowNull = true;
                col.DbType = FieldDataType.ToDbType(prop.PropertyType.Name);
                col.IsFK = false;
                col.IsIdentity = false;
                col.IsIndexed = false;
                col.IsPK = false;
                col.Ordinal = cols.Count;
                col.Precision = 0;
                col.Scale = 0;
                col.Size = 255;
                if (FieldDataType.IsBinaryType(col.DbType))
                {
                    col.Size = 400000000;
                }
                else if (FieldDataType.isBoolean(col.DbType))
                {
                    col.Size = 1;
                }
                else if (FieldDataType.IsDateTime(col.DbType))
                {
                    col.Size = 8;
                }
                else if (FieldDataType.IsNumber(col.DbType))
                {
                    col.Size = 8;
                }
                else if (FieldDataType.IsStringType(col.DbType))
                {
                    col.Size = 255;
                }
                else if (FieldDataType.IsTextType(col.DbType))
                {
                    col.Size = 400000000;
                }

                if (this._Instruction.PK.ToLower() == col.ColumnName.ToLower())
                {
                    col.IsPK = true;
                    col.IsIdentity = this._Instruction.IsIdentity;
                    col.IsIndexed = true;
                }

                cols.Add(col);
            }
            return cols;
        }

        #endregion
    }
}
